﻿using System;
using System.Data;
using System.Data.SqlServerCe;
using System.Collections.Generic;
using System.Windows.Forms;
using Home.Mobilesales.Common;
using Home.MobileSales.Common.Entities;


namespace Home.MobileSales.Common.Models
{
    public class VisitRepository
    {
        private List<Visit> LoadVisits(SqlCeCommand command, bool isForExport)
        {
            List<Visit> visits = new List<Visit>(5);
            using (SqlCeDataReader dr = command.ExecuteReader())
            {
                Visit visit;
                int idVisit = dr.GetOrdinal("IdVisit");
                int isReadOnly = dr.GetOrdinal("IsExport");
                int timeEndVisit = dr.GetOrdinal("TimeEndVisit");
                int buyPointId = dr.GetOrdinal("BuyPointId");
                int dateVisist = dr.GetOrdinal("DateVisit");
                int gpsData = dr.GetOrdinal("GpsData");
                while (dr.Read())
                {
                    visit = new Visit();
                    visit.DateVisit = dr.GetDateTime(dateVisist).Date.ToString();
                    visit.IsOrder = false;
                    visit.IdVisit = dr.GetInt32(idVisit);
                    visit.IsVisit = dr.IsDBNull(timeEndVisit) ? false : true;
                    visit.IsReadOnly = dr.GetBoolean(isReadOnly);
                    visit.TimeEndVisit = dr.IsDBNull(timeEndVisit)
                                             ? ""
                                             : dr.GetValue(timeEndVisit).ToString();
                    visit.GpsPosition = dr.IsDBNull(gpsData)?string.Empty:dr.GetString(gpsData);
                    visit.BuyPoint = DataManager.GetInstance().BuyPoints.GetBuyPointById(dr.GetInt32(buyPointId));

                    visit.Orders = isForExport
                                       ? DataManager.GetInstance().Orders.GetOrdersByVisit(visit.IdVisit, true)
                                       : DataManager.GetInstance().Orders.GetOrdersByVisit(visit.IdVisit, false);
                    
                    if (visit.Orders.Count > 0)
                        visit.IsOrder = true;
                    visits.Add(visit);
                }
                return visits;
            }
        }
      
        //public List<Visit> GetVisitsByDate(DateTime dateWork)
        //{
        //    DateTime endDate = (dateWork.AddHours(23).AddMinutes(59).AddSeconds(59)).Date; 
        //    return GetVisitsByDate(dateWork, endDate);
        //}

        public List<Visit> GetVisitsByDate(DateTime dateWork, bool isForExport)
        {
            DateTime endDate = (dateWork.AddHours(23).AddMinutes(59).AddSeconds(59)).Date;
            return GetVisitsByDate(dateWork, endDate, isForExport);
        }

        //public List<Visit> GetVisitsByDate(DateTime beginDateWork, DateTime endDateWork)
        //{
        //    using (SqlCeConnection connection = WorkDataBase.CreateConnection())
        //    {
        //        List<Visit> visits = new List<Visit>(5);
        //        if (connection.State != ConnectionState.Open)
        //            connection.Open();
        //        string sqlQuery =
        //            "SELECT IdVisit, DateVisit, BuyPointId, TimeEndVisit, Coord, IsExport FROM Visits" +
        //            " where DateVisit between @startDateVisits and @endDateVisits";
        //        using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
        //        {
        //            command.Parameters.Add("@startDateVisits", beginDateWork.Date);
        //            command.Parameters.Add("@endDateVisits", endDateWork.Date);
        //            return LoadVisits(command, false);
        //        }
        //    }
        //}
        
        public List<Visit> GetVisitsByDate(DateTime beginDateWork, DateTime endDateWork, bool isForExport)
        {
            using (SqlCeConnection connection = WorkDataBase.CreateConnection())
            {
                List<Visit> visits = new List<Visit>(5);
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                string sqlQuery =
                    "SELECT IdVisit, DateVisit, BuyPointId, TimeEndVisit, GpsData, IsExport, GpsData FROM Visits" +
                    " where DateVisit between @startDateVisits and @endDateVisits ";
                if (isForExport)
                    sqlQuery += "and TimeEndVisit is Not Null";
                using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
                {
                    command.Parameters.Add("@startDateVisits", beginDateWork.Date);
                    command.Parameters.Add("@endDateVisits", endDateWork.Date);
                    return LoadVisits(command, isForExport);
                }
            }
            
        }

        public List<Visit> GetAllVisitsForExport()
        {
            using (SqlCeConnection connection = WorkDataBase.CreateConnection())
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                string sqlQuery =
                    "SELECT IdVisit, DateVisit, BuyPointId, TimeEndVisit, GpsData, IsExport, GpsData FROM Visits" +
                    " where IsExport = 0 and TimeEndVisit is Not Null";
                using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
                {
                    return LoadVisits(command, true);
                }
            }

        }
    
        public void CreateVisit(Visit visit)
        {

            using (SqlCeConnection connection = WorkDataBase.CreateConnection())
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                string sqlQuery = "insert INTO Visits (DateVisit, BuyPointId) values (@DateVisit, @BuyPointId)";
                using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
                {
                    command.Parameters.Add("@DateVisit", SqlDbType.DateTime).Value = visit.DateVisit;
                    command.Parameters.Add("@BuyPointId", SqlDbType.Int).Value = visit.BuyPoint.IdBuyPoint;
                    if (command.ExecuteNonQuery() < 1)
                    {
                        throw new OtherException("Не добавлено жодного запису");
                    }
                }
            }

        }

        public void UpdateVisit(Visit visit)
        {
            using (SqlCeConnection connection = WorkDataBase.CreateConnection())
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                string sqlQuery = "Update Visits set " +
                                  "TimeEndVisit = @TimeEndVisit, IsExport = @IsExport, GpsData = @GpsData where idVisit = @IdVisit";
                using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
                {
                    command.Parameters.Add("@GpsData", visit.GpsPosition);
                    command.Parameters.Add("@TimeEndVisit", SqlDbType.DateTime).Value = visit.TimeEndVisit;
                    command.Parameters.Add("@IsExport", SqlDbType.Bit).Value = visit.IsReadOnly;
                    command.Parameters.Add("@IdVisit", SqlDbType.Int).Value = visit.IdVisit;
                    if (command.ExecuteNonQuery() < 1)
                    {
                        throw new OtherException("Не змінено жодного запису");
                    }
                }
            }

        }

        public void DeleteVisit(int id)
        {
            using (SqlCeConnection connection = WorkDataBase.CreateConnection())
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                string sqlQuery = "Delete from Visits where IdVisit = @IdVisit";
                using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
                {
                    command.Parameters.Add("@IdVisit", id);
                    if (command.ExecuteNonQuery() < 1)
                    {
                        throw new OtherException("Не видалено жодного запису");
                    }
                }
            }
        }

        public void LoadFromDate(DateTime dateTime)
        {
            using (SqlCeConnection connection = WorkDataBase.CreateConnection())
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                string sqlQuery =
                        "insert INTO Visits (DateVisit, BuyPointId) SELECT @DateVisit "+
                        "AS DateVisit, BuyPointId FROM GraphicsVisits WHERE " + dateTime.DayOfWeek.ToString() +
                        " = 1";
                using (SqlCeCommand command = new SqlCeCommand(sqlQuery, connection))
                {
                    command.Parameters.Add("@DateVisit", dateTime.Date);
                    if (command.ExecuteNonQuery() < 1)
                    {
                        throw new OtherException("Не добавлено жодного запису");
                    }
                }
            }
        }

    }
}

